package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import CONEXAO.Conexao;
import CORE.GenericDAO;
import DTO.AutorDTO;
import DTO.ObraDTO;

public class AutorDAO extends GenericDAO<AutorDTO> {

	public AutorDAO() {
		super("autor", AutorDTO.class);
	}
		
	public List<AutorDTO> pesquisarPorNome(AutorDTO autorDTO){
		try {
			List<AutorDTO> autores = new ArrayList<>();
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT * FROM ");
            sql.append(tabela);
            sql.append(" WHERE nome LIKE ?");
            
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setString(1, "%"+autorDTO.getNome()+"%");
            
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
            	autores.add(criarObjeto(rs));
            }
                        
            rs.close();
            ps.close();
            
            conn.close();
            
            return autores;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean existe(AutorDTO autorDTO){
		try {
			AutorDTO autor = null;
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT * FROM ");
            sql.append(tabela);
            sql.append(" WHERE nome = ?");
            
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setString(1, autorDTO.getNome());
            
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
            	autor = criarObjeto(rs);
            }
                        
            rs.close();
            ps.close();
            
            conn.close();
			return autor != null;
		} catch (Exception e) {
			return false;
		}
	}
	
	public List<AutorDTO> pesquisarAutoresDaObra(ObraDTO obra){
		try {
			List<AutorDTO> autores = new ArrayList<AutorDTO>();
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT a.* FROM ");
            sql.append("autor a, autores aut, obra o ");
            sql.append("WHERE a.codigo = aut.autor ");
            sql.append("AND o.codigo = aut.obra ");
            sql.append("AND o.codigo = (?)");
            
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setLong(1, obra.getCodigo());
            
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
            	autores.add(criarObjeto(rs));
            }
                        
            rs.close();                        
            conn.close();
            
            return autores;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public List<AutorDTO> naoPesquisarAutoresDaObra(ObraDTO obra){
		try {
			List<AutorDTO> autores = new ArrayList<AutorDTO>();
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT * FROM ");
            sql.append(tabela);                       
            sql.append(" WHERE codigo NOT IN ( ");
            	sql.append("SELECT a.codigo FROM ");
            	sql.append("autor a, autores aut, obra o ");
            	sql.append("WHERE a.codigo = aut.autor ");
            	sql.append("AND o.codigo = aut.obra ");
            	sql.append("AND o.codigo = (?) )");
            
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setLong(1, obra.getCodigo());
            
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
            	autores.add(criarObjeto(rs));
            }
                        
            rs.close();                        
            conn.close();
            
            return autores;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public List<AutorDTO> pesquisar4AutoresAleatorios(){
		try {
			List<AutorDTO> autores = new ArrayList<AutorDTO>();
            Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();                        
            sql.append("SELECT * FROM ");
            sql.append(tabela);                       
            sql.append(" ORDER BY rand() LIMIT 4;");
                                               
            ResultSet rs = conn.prepareStatement(sql.toString()).executeQuery();
            while(rs.next()){
            	autores.add(criarObjeto(rs));
            }
                        
            rs.close();                        
            conn.close();
            
            return autores;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}		 
	}
	
	@Override
	public void setarPSExcluir(PreparedStatement ps, AutorDTO autor) throws SQLException{
		ps.setLong(1, autor.getCodigo());
	}
	
	@Override
	public void setarPSAlterar(PreparedStatement ps, AutorDTO autor) throws SQLException{
		setarPSIncluir(ps, autor);
		ps.setLong(4, autor.getCodigo());
	}
	
	@Override
	public void setarPSIncluir(PreparedStatement ps, AutorDTO autor) throws SQLException{
		ps.setString(1, autor.getDescricao());
		ps.setString(2, autor.getNome());
		ps.setBytes(3, autor.getImagem());
	}
	
	@Override
	public AutorDTO criarObjeto(ResultSet rs) throws SQLException {
		AutorDTO autor = new AutorDTO();
		autor.setCodigo(rs.getLong(1));
		autor.setNome(rs.getString(2));
		autor.setDescricao(rs.getString(3));
		autor.setImagem(rs.getBytes(4));
		return autor;
	}		
}